USE [BSE]
GO
/****** Object:  View [dbo].[VW_Vouchers]    Script Date: 03/07/2020 12:13:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*---------------------------------------------------------------------------------------------------------------------*/
ALTER VIEW [dbo].[VW_Vouchers]
AS
SELECT     dbo.Voucher.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, dbo.SessionInfo.SessionFrom, 
                      dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.ReadOnly, dbo.VoucherDetails.VoucherDetailID, 
                      dbo.VoucherDetails.AccountNo, Accounts_5.AccountTitle, dbo.VoucherDetails.Remarks, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE - 1 * dbo.VoucherDetails.Amount END AS Amount, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Cr') THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, 
                      dbo.VoucherDetails.DrOrCr, Accounts_5.ParentAccount, dbo.Voucher.BankAccountID, dbo.Voucher.LoginID, dbo.Employees.EmployeeName, dbo.Voucher.HostName, 
                      dbo.Voucher.EntryDateTime, Accounts_2.AccountTitle AS BankAccountTitle, dbo.VoucherDetails.Amount AS ActualAmount, dbo.Voucher.ModifyID, 
                      Employees_1.EmployeeName AS ModifyName, dbo.Voucher.ModifyHostName, dbo.Voucher.ModifyDateTime, dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, 
                      dbo.vw_Items.ItemName, dbo.vw_Items.ItemNameUrdu, dbo.vw_Items.InUnit, dbo.VoucherDetails.Qty, dbo.VoucherDetails.Rate, dbo.vw_Items.GroupID, 
                      dbo.vw_Items.GroupName, dbo.Voucher.EFormNo, dbo.Voucher.ReturnDate, dbo.Voucher.AgentID, Accounts_1.AccountTitle AS AgentName, 
                      Accounts_1.Remarks AS AgentAddress, dbo.Voucher.PartyID, Accounts_3.AccountTitle AS PartyName, Accounts_3.CellNo AS PartyCellNo, 
                      Accounts_3.Phone AS PartyPhoneNo, Accounts_3.Remarks AS PartyAddress, dbo.Voucher.BLDate, dbo.Voucher.BLNo, dbo.Voucher.ContainerTypeID, 
                      Groups_2.GroupName AS ContainerType, Groups_2.GroupNameUrdu AS ContainerTypeUrdu, dbo.Voucher.NoOfContainers, dbo.Voucher.ShipID, 
                      Groups_1.GroupName AS ShipName, Groups_1.GroupNameUrdu AS ShipNameUrdu, dbo.VoucherDetails.ShipMarkID, dbo.Groups.GroupName AS ShipMark, 
                      dbo.VoucherDetails.PackageID, Groups_3.GroupName AS Packages, dbo.VoucherDetails.Scale, dbo.VoucherDetails.ProductRemarks, dbo.VoucherDetails.NetWeight, 
                      dbo.VoucherDetails.GrossWeight, dbo.VoucherDetails.GrossWeightTotal, dbo.VoucherDetails.RateRemarks, dbo.Voucher.SupplierID, 
                      dbo.Accounts.AccountTitle AS SupplierName, dbo.Accounts.CellNo AS SupplierCellNo, dbo.Accounts.Phone AS SupplierPhoneNo, 
                      dbo.Accounts.Remarks AS SupplierAddress, dbo.Voucher.TransporterID, Accounts_4.AccountTitle AS TransporterName, Accounts_4.CellNo AS TransporterCellNo, 
                      Accounts_4.Phone AS TransporterPhoneNo, Accounts_4.Remarks AS TransporterAddress, dbo.Voucher.VoyNo, dbo.VoucherDetails.Type, 
                      dbo.VoucherDetails.FreightType, dbo.VoucherDetails.SaleDate, ISNULL(Accounts_5.Currency, 'PKR') AS Currency, dbo.Voucher.ContainerNo, 
                      dbo.Voucher.MiscAccountID, dbo.Voucher.NetInvoice, dbo.VoucherDetails.QTYSale, dbo.VoucherDetails.QTYDamage, dbo.Voucher.ShipLineID, 
                      Groups_4.GroupName AS ShipLine, Groups_4.GroupNameUrdu AS ShipLineUrdu, Accounts_3.AreaID, dbo.Area.AreaName, dbo.Area.AreaNameUrdu, 
                      dbo.Voucher.SubPartyID, ISNULL(Groups_5.GroupName, '.') AS SubPartyName, dbo.Voucher.GD, dbo.VoucherDetails.QTYLoad
FROM         dbo.Accounts AS Accounts_1 RIGHT OUTER JOIN
                      dbo.Area RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_3 ON dbo.Area.AreaID = Accounts_3.AreaID RIGHT OUTER JOIN
                      dbo.Groups AS Groups_2 RIGHT OUTER JOIN
                      dbo.Groups AS Groups_1 RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_4 RIGHT OUTER JOIN
                      dbo.Groups AS Groups_4 RIGHT OUTER JOIN
                      dbo.Groups AS Groups_5 RIGHT OUTER JOIN
                      dbo.Voucher ON Groups_5.GroupID = dbo.Voucher.SubPartyID ON Groups_4.GroupID = dbo.Voucher.ShipLineID ON 
                      Accounts_4.AccountNo = dbo.Voucher.TransporterID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.Voucher.SupplierID = dbo.Accounts.AccountNo ON Groups_1.GroupID = dbo.Voucher.ShipID ON 
                      Groups_2.GroupID = dbo.Voucher.ContainerTypeID ON Accounts_3.AccountNo = dbo.Voucher.PartyID ON 
                      Accounts_1.AccountNo = dbo.Voucher.AgentID LEFT OUTER JOIN
                      dbo.Groups RIGHT OUTER JOIN
                      dbo.VoucherDetails LEFT OUTER JOIN
                      dbo.Groups AS Groups_3 ON dbo.VoucherDetails.PackageID = Groups_3.GroupID ON dbo.Groups.GroupID = dbo.VoucherDetails.ShipMarkID ON 
                      dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.VoucherDetails.ItemID = dbo.vw_Items.ItemID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_5 ON dbo.VoucherDetails.AccountNo = Accounts_5.AccountNo LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_2 ON dbo.Voucher.BankAccountID = Accounts_2.AccountNo LEFT OUTER JOIN
                      dbo.Employees AS Employees_1 ON dbo.Voucher.ModifyID = Employees_1.EmployeeID LEFT OUTER JOIN
                      dbo.Employees ON dbo.Voucher.LoginID = dbo.Employees.EmployeeID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID